<?php
//$Id: DbTable.php 518 2012-08-08 16:30:21Z tomqin@gmail.com $

/**
 * DbTable 数据表操作类
 *
 * 实现数据表的等基本操作。数据结构一般是基于主键的。为了方便起见，这里规定：
 * 主键只能有一个，且类型须为整型。
 *
 * @copyright       Copyright (C) 2010-2012 ARESCMS Inc.
 * @author          TomQin <tomqin@gmail.com>
 * @license         http://www.arescms.cn/license/
 * @package Core
 */
class DbTable{
    protected $_table  = '';
    protected $_pk     = '';
    protected $_auto   = false;
    protected $_fields = array();
    protected $sql = array('field'=>'', 'where'=>'', 'order'=>'', 'limit'=>'', 'group'=>'', 'having'=>'');

    function __construct($table){
        $this->_table = $table;
        $this->initTable();
    }

    function tableName(){
        return $this->_table;
    }

    function emptySql(){
        $this->sql = array('field'=>'', 'where'=>'', 'order'=>'', 'limit'=>'', 'group'=>'', 'having'=>'');
    }

    /**
     * 自动获取表结构
     */
    private function initTable(){
        $cachefile = SYSTEM_ROOT."data/tables/".$this->tableName().".db.php";
        //$this->tabName=TABPREFIX.$table; //加前缀的表名

        if(file_exists($cachefile)){
            $line = file($cachefile);
            $this->_auto   = intval($line[1]) ? true : false;
            $this->_pk     = trim($line[2]);
            $this->_fields = json_decode($line[3]);
        }else{
            $result = Db::query("DESC ". $this->tableName());
            $fields = array();
            $prikey = '';
            $auto   = 0;
            $prikey = '';
            while($row = $result->fetch_assoc()){
                $fields[] = strtolower($row['Field']);
                if($row['Key'] == 'PRI'){
                    $prikey = strtolower($row['Field']);
                }
                if($row['Extra'] == 'auto_increment'){
                    $auto = 1;
                }
            }

            $this->_fields = $fields;
            $this->_auto   = $auto;
            $this->_pk     = $prikey;

            $data = "<?php exit();?>\r\n".intval($this->_auto)."\r\n".trim($this->_pk)."\r\n".json_encode($this->_fields);
            file_put_contents($cachefile, $data);
        }
    }

    /*
        select （查询多条记录的操作，返回二维数组）
        find   （查询一条记录的操作，返回一维数组）
        total  （按条件查询数据表的总记录数）
        insert （插入数据的操作，返回最后插入的ID）
        update （更新数据表的操作，返回影响的行数）
        delete （删除数据表的操作，返回影响的行数）
    */

    /**
     * 设置字段操作
     *
     * @param string|array      field names
     * @return DbTable
    **/
    function field($var){
        if($var){
            if(is_array($var)){
                $this->sql['field'] = join(',', $var);
            }else{
                $this->sql['field'] = $var;
            }
        }else{
            $this->sql['field'] = '*';
        }
        return $this;
    }

    /**
     * 设置 WHERE 条件
     *
     * @param int|array|string  where condition
     * @usage
     *     array    $table->where(array('age' => 20));  ==>  WHERE age = 20
     *     string   $table->where('age > 10');          ==>  WHERE id > 10
     * @return DbTable
    **/
    function where($var){
        $_1 = $this->sql['where'] = trim($this->sql['where']);
        if(is_array($var)){
            $sql = '';
            $conn = ($this->sql['where'] == '') ? '' : ' AND ';
            foreach($var as $k => $v){
                $sql .= $conn . $this->_fieldValue($k, $v);
                $conn = ' AND ';
            }
        }else{//string
            $sql = (string)$var;
        }

        $this->sql['where'] .= ' '.trim($sql).' ';
        return $this;
    }

    /**
     * 设置基于主键的 WHERE 条件
     *
     * @param int|array     prime key
     * @usage
     *     int      $table->pkWhere(1);                 ==>  WHERE id = 1
     *     array    $table->pkWhere(array(1,2,3));      ==>  WHERE id IN (1,2,3)
     * @return DbTable
    **/
    function pkWhere($var){
        if($var){
            $this->where(array($this->_pk => $var));
        }
        return $this;
    }

    /**
     * 生成字段/值对
     *
     * @param string        field name
     * @param string|array  field value
     * @return string
    **/
    private function _fieldValue($key, $val){
        if(is_array($val)){
            return "`$key` IN ('" .join("', '", Db::escapeString($val)). "')";
        }else if(is_null($val)){
            return "`$key` is NULL";
        }else{
            return "`$key` = '{$val}'";
        }
    }

    /**
     * 查询多条记录的操作，返回二维数组
     *
     * @param int|array     prime key
     * @return array
    **/
    function select($id){
        $this->pkWhere($id);

        $query = $this->buildSql('select');

        return Db::getAll($query);
    }

    /**
     * 查询一条记录的操作，返回一维数组
     *
     * @param int|array     prime key
     * @return array
    **/
    function find($id, $field = '*'){
        $this->field($field);
        $this->pkWhere($id);

        $query = $this->buildSql('select');

        return Db::getRow($query);
    }

    /**
     * 按条件查询数据表的总记录数
     *
     * @param int|array     prime key
     * @return array
    **/
    function total($field = ''){
        if(empty($field) || in_array($field, $this->_fields))
            $field = $this->_pk ? $this->_pk : '*';
        return $this->find('COUNT(`{$field}`)');
    }

    /**
     * 插入数据的操作，返回最后插入的ID
     *
     * @param array
     * @return int
    **/
    function insert(array $arr){

    }

    /**
     * 更新数据表的操作，返回影响的行数
     *
     * @param array
     * @param array|int     prime key
     * @return int
    **/
    function update(array $arr, $id = null){
        $list = $conn = '';
        $fields = array_diff($this->_fields, array($this->_pk));
        foreach($fields as $key){
            if(isset($arr[$key])){
                $list .= $conn . $this->_fieldValue($key, (string)$arr[$key]);
                $conn  = ',';
            }
        }
        $id = $id  ? $id : (isset($arr[$this->_pk]) ? $arr[$this->_pk] : null);
        $this->pkWhere($id);
        $where = $this->sql['where'] != '' ? 'WHERE '.$this->sql['where'] : '';
        $order = $this->sql['order'] != '' ? "ORDER BY {$this->sql['order'][0]}" : '';
        $limit = $this->sql["limit"] != '' ? $this->praseLimit($this->sql['limit']) : '';

        $this->emptySql();
        $query = "UPDATE `{$this->_table}` SET {$list} {$where} {$order} {$limit}";
p($query, 'UPDATE SQL');
    }

    /**
     * 删除数据表的操作，返回影响的行数
     *
     * @param int|array     prime key
     * @return int
    **/
    function delete($id){
        $this->pkWhere($id);
        $where = $this->sql['where'] != '' ? 'WHERE '.$this->sql['where'] : '';
        $order = $this->sql['order'] != '' ? "ORDER BY {$this->sql['order'][0]}" : '';
        $limit = $this->sql["limit"] != '' ? $this->praseLimit($this->sql['limit']) : '';

        $this->emptySql();
        $query = "DELETE FROM `{$this->_table}` {$where} {$order} {$limit}";
p($query, 'DELETE SQL');
        //$stmt = Db::query($query);
        //return $stmt->affected_num;
    }

    /**
     * 根据类型生成 SQL 语句
     *
     * @param string    select/insert/replace/update/delete
     * @return string
    **/
    function buildSql($type){
        $field = $this->sql['field'];
        $where = $this->sql['where'] != '' ? 'WHERE '.$this->sql['where'] : '';
        $order = $this->sql['order'] != '' ? "ORDER BY {$this->sql['order'][0]}" : '';
        $limit = $this->sql["limit"] != '' ? $this->praseLimit($this->sql['limit']) : '';

        $this->emptySql();
        switch($type){
            case 'select' :
                $sql = "SELECT {$field} FROM `".$this->tableName()."` {$where} {$order} {$limit}";
        }
        //p($sql);
        return $sql;
    }
}
?>